﻿Imports System.Data
Imports System.Data.SqlClient

Public Class UsuariosDAL
    
    Sub New()

    End Sub

    Function inserir(pessoa As Integer, login As String, senha As String, ativo As Boolean) As Integer
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim id As Integer
        Try
            cmd.CommandText = "insert into usuarios(pes_codigo, usu_login, usu_senha, usu_ativo, usu_data_cadastro) " & _
                "values(@pes, @login, @senha, @ativo, getdate())"
            cmd.Parameters.Add(New SqlParameter("@pes", SqlDbType.Int)).Value = pessoa
            cmd.Parameters.Add(New SqlParameter("@login", SqlDbType.VarChar, 20)).Value = login
            cmd.Parameters.Add(New SqlParameter("@senha", SqlDbType.VarChar, 15)).Value = senha
            cmd.Parameters.Add(New SqlParameter("@ativo", SqlDbType.Bit)).Value = ativo
            id = objD.executacomandoAI(cmd, "rotas")
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return id
    End Function

    Sub alterar(usuario As Integer, pessoa As Integer, login As String, senha As String, ativo As Boolean)
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Try
            cmd.CommandText = "update usuarios set pes_codigo=@pes, usu_login=@login, usu_senha=@senha, " & _
                "usu_ativo=@ativo where usu_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = usuario
            cmd.Parameters.Add(New SqlParameter("@pes", SqlDbType.Int)).Value = pessoa
            cmd.Parameters.Add(New SqlParameter("@login", SqlDbType.VarChar, 20)).Value = login
            cmd.Parameters.Add(New SqlParameter("@senha", SqlDbType.VarChar, 15)).Value = senha
            cmd.Parameters.Add(New SqlParameter("@ativo", SqlDbType.Bit)).Value = ativo
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub

    Sub excluir(usuario As Integer)
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Try
            cmd.CommandText = "delete from usuarios where usu_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = usuario
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub

    Function localizar(campo As String, valor As String) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Try
            cmd.CommandText = "select p.pes_nome, u.* from usuarios u inner join pessoa p on u.pes_codigo=p.pes_codigo " & _
                "where " + campo + " like @valor"
            cmd.Parameters.Add(New SqlParameter("@valor", SqlDbType.VarChar)).Value = "%" + valor + "%"
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function localizar(ByVal codigo As Integer) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Try
            cmd.CommandText = "select * from usuarios where usu_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function localizar(ByVal descricao As String) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            If (descricao = "ultimo") Then
                cmd.CommandText = "SELECT usu_codigo = max(usu_codigo) FROM usuarios "
            ElseIf (descricao = "primeiro") Then
                cmd.CommandText = "SELECT usu_codigo = min(usu_codigo) FROM usuarios "
            End If
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function localizarProxAnterior(ByVal descricao As String, ByVal codigo As Integer) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            If (descricao = "proximo") Then
                cmd.CommandText = "SELECT usu_codigo FROM usuarios WHERE usu_codigo = (SELECT MIN(usu_codigo) FROM usuarios WHERE usu_codigo > @codigo)"

            ElseIf (descricao = "anterior") Then
                cmd.CommandText = "SELECT usu_codigo FROM usuarios  WHERE usu_codigo = (SELECT MAX(usu_codigo) FROM usuarios WHERE usu_codigo < @codigo)"
            End If
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function localizarPessoa(ByVal nome As String) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Try
            cmd.CommandText = "select * from pessoa where pes_nome like @nome and pes_tipo_fj = 'F'"
            cmd.Parameters.Add(New SqlParameter("@nome", SqlDbType.VarChar, 150)).Value = "%" + nome + "%"
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function localizarPessoa(codigo As Integer)
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Try
            cmd.CommandText = "select pes_nome from pessoa where pes_codigo=@codigo and pes_tipo_fj = 'F'"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Sub inserirPermissao(usu As Integer, permissao As Integer)
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Try
            cmd.CommandText = "insert into permissao_usuario(usu_codigo, permiss) " & _
                "values(@usu, @permissao)"
            cmd.Parameters.Add(New SqlParameter("@usu", SqlDbType.Int)).Value = usu
            cmd.Parameters.Add(New SqlParameter("@permissao", SqlDbType.VarChar)).Value = permissao
            objD.executacomandoAI(cmd, "rotas")
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub

    Sub excluirPermissao(usu As Integer)
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Try
            cmd.CommandText = "delete from permissao_usuario where usu_codigo=@usu"
            cmd.Parameters.Add(New SqlParameter("@usu", SqlDbType.Int)).Value = usu
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub

    Function localizarPermissao(usu As Integer) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Try
            cmd.CommandText = "select * from permissao_usuario where usu_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = usu
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function verificarLogin(login As String, senha As String) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Try
            cmd.CommandText = "select * from usuarios where usu_login=@login and usu_senha=@senha"
            cmd.Parameters.Add(New SqlParameter("@login", SqlDbType.VarChar, 20)).Value = login
            cmd.Parameters.Add(New SqlParameter("@senha", SqlDbType.VarChar, 15)).Value = senha
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Public Sub apagar()
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Try
            cmd.CommandText = "delete from permissao_usuario; delete from usuarios;"
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub

End Class
